3. SQL Row-Level Functions

PDF Slides

Single-row (row-level) functions in SQL:

• String functions
• Numeric functions
• Date & time functions (including formats & casting)
• NULL-handling functions
• CASE expressions (CASE WHEN)

They combine information from the slides SQL Functions and the scripts you provided.

1. Short summary

Row-level functions take input values from each row and return one output value per row (they don’t group rows like aggregates). Examples:

• Clean / transform text (TRIM, LOWER, REPLACE)
• Format or calculate numbers (ROUND, ABS)
• Extract or format dates (DATEPART, FORMAT, DATEADD)
• Handle NULLs safely (COALESCE, ISNULL, NULLIF)
• Build conditional logic inside queries (CASE WHEN)

You often nest these functions: LEN(TRIM(first_name)), FORMAT(DATEADD(year,1,OrderDate),'yyyy-MM'), etc.

2. Big picture – where does this fit?

The slide “SQL Functions” shows a tree:

• SQL Functions

  • Single-Row Functions (this chapter)

    • String
    • Numeric
    • Date & Time
    • NULL

  • Multi-Row Functions

    • Aggregate (SUM, AVG, …)
    • Window functions (advanced)

These notes are about Single-Row Functions, which are used mainly in the SELECT, WHERE, JOIN, GROUP BY, and ORDER BY clauses to compute, clean, and transform data at row level.

Think of them as Excel-style functions but in SQL, applied to each row.

3. Key concepts & definitions

4. Syntax cheat-sheet (templates)

4.1 String functions

-- Concatenate
CONCAT(string1, string2, ...)

-- Change case
LOWER(string_expression)
UPPER(string_expression)

-- Trim spaces at both ends
TRIM(string_expression)

-- Replace text
REPLACE(string_expression, old_substring, new_substring)

-- Length
LEN(string_expression)

-- Substring extraction
LEFT(string_expression, number_of_chars)
RIGHT(string_expression, number_of_chars)
SUBSTRING(string_expression, start_position, length)

These align with the string function diagrams on pages 7–15 (manipulation, calculation, extraction).

4.2 Numeric functions

-- Round to n decimal places
ROUND(numeric_expression, decimal_places)

-- Absolute value
ABS(numeric_expression)

Slide on page 18 visualizes how ROUND(3.516, 2), ROUND(3.516, 1), ROUND(3.516, 0) behave.

4.3 Date & time functions

Basic date/time & parts

GETDATE()                             -- current system datetime

DATETRUNC(datepart, date_value)
DATEPART(datepart, date_value)
DATENAME(datepart, date_value)
YEAR(date_value)
MONTH(date_value)
DAY(date_value)
EOMONTH(date_value)

Examples of datepart (from the “Date Parts” slide and script): year, quarter, month, day, week, weekday, hour, minute, second, millisecond, iso_week, plus abbreviations like yy, mm, dd, wk, etc.

Formatting & casting

FORMAT(value, format_string [, culture])
CONVERT(target_data_type, value [, style])
CAST(value AS target_data_type)

• FORMAT uses .NET-style format codes (e.g. 'dd/MM/yyyy', 'N2').
• CONVERT uses style numbers (e.g. 23, 101).
• CAST is simpler and does not accept formats/styles.

Date calculations

DATEADD(datepart, number, date_value)
DATEDIFF(datepart, start_date, end_date)
ISDATE(value)    -- returns 1 or 0

4.4 NULL & CASE

-- NULL handling
ISNULL(expression, replacement)
COALESCE(expr1, expr2, ..., exprN)
NULLIF(expr1, expr2)
-- Compare to NULL
expression IS NULL
expression IS NOT NULL

-- CASE (long form)
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result   -- optional
END

-- CASE (quick form / simple CASE)
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

Slides show both the “full form” (conditions after WHEN) and “quick form” where you compare a single column to many values.

5. Worked examples (with explanations)

5.1 String functions on customers table

5.1.1 CONCAT – combine columns

SELECT 
    CONCAT(first_name, '-', country) AS full_info
FROM customers;

• CONCAT joins first_name and country with a hyphen.
• Unlike +, CONCAT safely handles NULLs (in SQL Server it treats NULL as empty string).

5.1.2 LOWER / UPPER – normalize case

SELECT 
    LOWER(first_name) AS lower_case_name,
    UPPER(first_name) AS upper_case_name
FROM customers;

Use these to standardize text before comparisons or reporting. The slide “LOWER & UPPER” shows visually converting "Maria" ↔ "maria" in both directions.

5.1.3 TRIM – remove leading/trailing spaces

SELECT 
    first_name,
    LEN(first_name)              AS len_name,
    LEN(TRIM(first_name))        AS len_trim_name,
    LEN(first_name) - LEN(TRIM(first_name)) AS flag
FROM customers
WHERE LEN(first_name) != LEN(TRIM(first_name));

• TRIM cleans spaces at both ends (see TRIM diagram on page 11).
• Comparing lengths helps find “dirty” names with hidden spaces.

5.1.4 REPLACE – swap or remove characters

-- Replace '-' with '/'
SELECT
    '123-456-7890' AS phone,
    REPLACE('123-456-7890', '-', '/') AS clean_phone;

-- Change file extension .txt → .csv
SELECT
    'report.txt' AS old_filename,
    REPLACE('report.txt', '.txt', '.csv') AS new_filename;

Slide shows that if you replace '-' with an empty string, you remove the character completely.

5.1.5 LEN – string length

SELECT 
    first_name, 
    LEN(first_name) AS name_length
FROM customers;

• Counts characters including spaces.
• Slide example counts characters in "Maria", "350", and a date string.

5.1.6 LEFT, RIGHT – fixed-size substrings

-- First 2 characters (after trimming)
SELECT 
    first_name,
    LEFT(TRIM(first_name), 2) AS first_2_chars
FROM customers;

-- Last 2 characters
SELECT 
    first_name,
    RIGHT(first_name, 2) AS last_2_chars
FROM customers;

• Useful for codes like "US123" → "US" or "23".
• Slide “LEFT & RIGHT” clearly shows indexes from both ends of "Maria".

5.1.7 SUBSTRING – flexible extraction

SELECT 
    first_name,
    SUBSTRING(TRIM(first_name), 2, LEN(first_name)) AS trimmed_name
FROM customers;

• SUBSTRING(value, start, length) – slide shows using LEN() for dynamic length to “take from position X until the end”.

5.1.8 Nesting string functions

SELECT
    first_name, 
    UPPER(LOWER(first_name)) AS nesting
FROM customers;

• Demonstrates nesting: apply LOWER then UPPER.
• The “Nested Function” slide shows a deeper example LEN(LOWER(LEFT('Maria', 2))).

5.2 Numeric functions

SELECT 
    3.516 AS original_number,
    ROUND(3.516, 2) AS round_2,
    ROUND(3.516, 1) AS round_1,
    ROUND(3.516, 0) AS round_0;

• ROUND(3.516, 2) → 3.52
• ROUND(3.516, 1) → 3.5
• ROUND(3.516, 0) → 4 (rounded to nearest whole number).

SELECT 
    -10 AS original_number,
    ABS(-10) AS absolute_value_negative,
    ABS(10)  AS absolute_value_positive;

• ABS removes the sign, helpful in distance/difference calculations.

5.3 Date & time functions

5.3.1 GETDATE & basic date display

SELECT
    OrderID,
    CreationTime,
    '2025-08-20' AS HardCoded,
    GETDATE() AS Today
FROM Sales.Orders;

• Shows the difference between stored date (CreationTime), hard-coded constants, and system date.

5.3.2 Extracting parts: DATETRUNC, DATENAME, DATEPART, YEAR/MONTH/DAY

SELECT
    OrderID,
    CreationTime,
    DATETRUNC(year,   CreationTime) AS Year_dt,
    DATETRUNC(day,    CreationTime) AS Day_dt,
    DATETRUNC(minute, CreationTime) AS Minute_dt,
    DATENAME(month,   CreationTime) AS Month_dn,
    DATENAME(weekday, CreationTime) AS Weekday_dn,
    DATEPART(year,    CreationTime) AS Year_dp,
    DATEPART(hour,    CreationTime) AS Hour_dp,
    YEAR(CreationTime)  AS Year,
    MONTH(CreationTime) AS Month,
    DAY(CreationTime)   AS Day
FROM Sales.Orders;

• DATETRUNC → beginning of the unit (start of year, day, minute).
• DATENAME → textual name ('August', 'Wednesday').
• DATEPART/YEAR/MONTH/DAY → integers (2025, 8, 20, etc.). The big comparison table on the “How to Choose the Right Function?” slide matches these outputs.

5.3.3 Aggregation by truncated dates

SELECT
    DATETRUNC(year, CreationTime) AS Creation,
    COUNT(*) AS OrderCount
FROM Sales.Orders
GROUP BY DATETRUNC(year, CreationTime);

• Truncate to year, group on that, count rows.
• Same pattern works for months, weeks, etc.

5.3.4 EOMONTH – last day of month

SELECT
    OrderID,
    CreationTime,
    EOMONTH(CreationTime) AS EndOfMonth
FROM Sales.Orders;

• Gives last calendar day of the month of CreationTime.

5.3.5 Typical “how many orders?” queries

-- Per year
SELECT 
    YEAR(OrderDate) AS OrderYear, 
    COUNT(*) AS TotalOrders
FROM Sales.Orders
GROUP BY YEAR(OrderDate);

-- Per month (numeric)
SELECT 
    MONTH(OrderDate) AS OrderMonth, 
    COUNT(*) AS TotalOrders
FROM Sales.Orders
GROUP BY MONTH(OrderDate);

-- Per month (friendly name)
SELECT 
    DATENAME(month, OrderDate) AS OrderMonth, 
    COUNT(*) AS TotalOrders
FROM Sales.Orders
GROUP BY DATENAME(month, OrderDate);

• Use YEAR / MONTH for easy grouping; DATENAME for human-readable month names.

5.3.6 FILTER by month (February example)

SELECT *
FROM Sales.Orders
WHERE MONTH(OrderDate) = 2;

• Alternative: WHERE DATENAME(month, OrderDate) = 'February' (but numeric is usually faster).

5.3.7 FORMAT – pretty dates and numbers

SELECT
    OrderID,
    CreationTime,
    FORMAT(CreationTime, 'MM-dd-yyyy') AS USA_Format,
    FORMAT(CreationTime, 'dd-MM-yyyy') AS EURO_Format,
    FORMAT(CreationTime, 'ddd')        AS Abbrev_Day,
    FORMAT(CreationTime, 'MMMM')      AS FullMonth
FROM Sales.Orders;

Slide and the extra script list date format specifiers (d, dd, ddd, MMMM, yyyy, HH, etc.) and number format specifiers (N, P, C, F, N1, N2, etc.).

Custom format example:

SELECT
    OrderID,
    CreationTime,
    'Day ' + FORMAT(CreationTime, 'ddd MMM') +
    ' Q' + DATENAME(quarter, CreationTime) + ' ' +
    FORMAT(CreationTime, 'yyyy hh:mm:ss tt') AS CustomFormat
FROM Sales.Orders;

5.3.8 FORMAT + GROUP BY

SELECT
    FORMAT(CreationTime, 'MMM yy') AS OrderDate,
    COUNT(*) AS TotalOrders
FROM Sales.Orders
GROUP BY FORMAT(CreationTime, 'MMM yy');

• Good for charts labeled like "Jan 25".

5.3.9 CONVERT – style-based casting

SELECT
    CONVERT(INT, '123') AS [String to Int CONVERT],
    CONVERT(DATE, '2025-08-20') AS [String to Date CONVERT],
    CreationTime,
    CONVERT(DATE, CreationTime) AS [Datetime to Date CONVERT],
    CONVERT(VARCHAR, CreationTime, 32) AS [USA Std. Style:32],
    CONVERT(VARCHAR, CreationTime, 34) AS [EURO Std. Style:34]
FROM Sales.Orders;

The slide and script give large style tables – e.g., style 23 = yyyy-mm-dd, 101 = mm/dd/yyyy, 120 = yyyy-mm-dd hh:mm:ss.

5.3.10 CAST – simple type conversion

SELECT
    CAST('123' AS INT)              AS [String to Int],
    CAST(123 AS VARCHAR)            AS [Int to String],
    CAST('2025-08-20' AS DATE)      AS [String to Date],
    CAST('2025-08-20' AS DATETIME2) AS [String to Datetime],
    CreationTime,
    CAST(CreationTime AS DATE)      AS [Datetime to Date]
FROM Sales.Orders;

• Difference from CONVERT: no style numbers; syntax is a bit more standard.

5.3.11 DATEADD / DATEDIFF – date arithmetic

SELECT
    OrderID,
    OrderDate,
    DATEADD(day,   -10, OrderDate) AS TenDaysBefore,
    DATEADD(month,  3, OrderDate)  AS ThreeMonthsLater,
    DATEADD(year,   2, OrderDate)  AS TwoYearsLater
FROM Sales.Orders;

SELECT
    EmployeeID,
    BirthDate,
    DATEDIFF(year, BirthDate, GETDATE()) AS Age
FROM Sales.Employees;

SELECT
    MONTH(OrderDate) AS OrderMonth,
    AVG(DATEDIFF(day, OrderDate, ShipDate)) AS AvgShip
FROM Sales.Orders
GROUP BY MONTH(OrderDate);

SELECT
    OrderID,
    OrderDate AS CurrentOrderDate,
    LAG(OrderDate) OVER (ORDER BY OrderDate) AS PreviousOrderDate,
    DATEDIFF(day, LAG(OrderDate) OVER (ORDER BY OrderDate), OrderDate) AS NrOfDays
FROM Sales.Orders;

• Shows DATEDIFF in basic use and combined with window function LAG for time gaps.

5.3.12 ISDATE – validate date strings

SELECT
    OrderDate,
    ISDATE(OrderDate) AS IsValidDate,
    CASE 
        WHEN ISDATE(OrderDate) = 1 THEN CAST(OrderDate AS DATE)
        ELSE '9999-01-01'
    END AS NewOrderDate
FROM (
    SELECT '2025-08-20' AS OrderDate UNION
    SELECT '2025-08-21' UNION
    SELECT '2025-08-23' UNION
    SELECT '2025-08'
) AS t;
-- WHERE ISDATE(OrderDate) = 0     -- to keep only invalid rows

• Useful before casting external text data to dates.

5.4 NULL functions & patterns

Slides explain what NULL means, where it comes from (missing data, outer joins, etc.), and several handling functions.

5.4.1 COALESCE & AVG – NULL-safe aggregation

SELECT
    CustomerID,
    Score,
    COALESCE(Score, 0) AS Score2,
    AVG(Score) OVER () AS AvgScores,
    AVG(COALESCE(Score, 0)) OVER () AS AvgScores2
FROM Sales.Customers;

• COALESCE(Score, 0) substitutes 0 when Score is NULL.
• Shows how averages change when you treat NULL as 0 instead of ignoring it.

5.4.2 COALESCE with concatenation & math

SELECT
    CustomerID,
    FirstName,
    LastName,
    FirstName + ' ' + COALESCE(LastName, '') AS FullName,
    Score,
    COALESCE(Score, 0) + 10 AS ScoreWithBonus
FROM Sales.Customers;

• For last names that are NULL, use empty string so full name still works.
• Add 10 bonus points even when Score is NULL.

5.4.3 Sorting with NULL at the end

SELECT
    CustomerID,
    Score
FROM Sales.Customers
ORDER BY 
    CASE WHEN Score IS NULL THEN 1 ELSE 0 END,  -- 0 first, 1 last
    Score;

• Clever trick so NULLs appear after all real scores.

5.4.4 NULLIF – avoid division by zero

SELECT
    OrderID,
    Sales,
    Quantity,
    Sales / NULLIF(Quantity, 0) AS Price
FROM Sales.Orders;

• If Quantity = 0, NULLIF(Quantity, 0) → NULL, and the division result becomes NULL instead of error.

5.4.5 IS NULL / IS NOT NULL

-- Customers without scores
SELECT *
FROM Sales.Customers
WHERE Score IS NULL;

-- Customers with scores
SELECT *
FROM Sales.Customers
WHERE Score IS NOT NULL;

Slides emphasize: use IS NULL, not = NULL.

5.4.6 LEFT ANTI JOIN pattern – “no match” rows

SELECT
    c.*,
    o.OrderID
FROM Sales.Customers AS c
LEFT JOIN Sales.Orders AS o
    ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;

• Returns customers who have no orders.
• This is called a left anti join. NULL appears on the right table side for non-matching rows.

5.4.7 NULL vs empty vs blank

WITH Orders AS (
    SELECT 1 AS Id, 'A' AS Category UNION
    SELECT 2, NULL UNION
    SELECT 3, '' UNION
    SELECT 4, '  '
)
SELECT 
    *,
    DATALENGTH(Category) AS LenCategory,
    TRIM(Category) AS Policy1,
    NULLIF(TRIM(Category), '') AS Policy2,
    COALESCE(NULLIF(TRIM(Category), ''), 'unknown') AS Policy3
FROM Orders;

• NULL = no value; '' = empty string; ' ' = spaces.
• Policy2 converts “empty after trimming” to NULL; Policy3 replaces those NULLs with 'unknown'. Slides have a diagram comparing NULL, empty, and blank spaces.

5.5 CASE statements

Slides introduce CASE syntax and use cases: categorizing sales into High/Medium/Low, mapping country names to codes, transforming gender codes, etc.

5.5.1 Categorize data

SELECT
    Category,
    SUM(Sales) AS TotalSales
FROM (
    SELECT
        OrderID,
        Sales,
        CASE
            WHEN Sales > 50 THEN 'High'
            WHEN Sales > 20 THEN 'Medium'
            ELSE 'Low'
        END AS Category
    FROM Sales.Orders
) AS t
GROUP BY Category
ORDER BY TotalSales DESC;

• Inner query uses CASE to create a Category column.
• Outer query aggregates by that category.

5.5.2 Mapping values

SELECT
    CustomerID,
    FirstName,
    LastName,
    Country,
    CASE 
        WHEN Country = 'Germany' THEN 'DE'
        WHEN Country = 'USA'     THEN 'US'
        ELSE 'n/a'
    END AS CountryAbbr
FROM Sales.Customers;

• Exactly like the “Germany → DE, France → FR, Italy → IT” example on the slide.

Quick form:

SELECT
    CustomerID,
    FirstName,
    LastName,
    Country,
    CASE Country
        WHEN 'Germany' THEN 'DE'
        WHEN 'USA'     THEN 'US'
        ELSE 'n/a'
    END AS CountryAbbr2
FROM Sales.Customers;

Here CASE Country means “compare Country with each WHEN value”.

5.5.3 Handling NULLs with CASE

SELECT
    CustomerID,
    LastName,
    Score,
    CASE
        WHEN Score IS NULL THEN 0
        ELSE Score
    END AS ScoreClean,
    AVG(
        CASE
            WHEN Score IS NULL THEN 0
            ELSE Score
        END
    ) OVER () AS AvgCustomerClean,
    AVG(Score) OVER () AS AvgCustomer
FROM Sales.Customers;

• Another way (besides COALESCE) to replace NULL before calculations.

5.5.4 Conditional aggregation

SELECT
    CustomerID,
    SUM(
        CASE
            WHEN Sales > 30 THEN 1
            ELSE 0
        END
    ) AS TotalOrdersHighSales,
    COUNT(*) AS TotalOrders
FROM Sales.Orders
GROUP BY CustomerID;

• CASE inside SUM counts only rows where condition holds.
• Very common pattern for “conditional counts”.

6. Common mistakes & tips

1. Using = NULL instead of IS NULL

   • Wrong: WHERE Score = NULL
   • Right: WHERE Score IS NULL

2. Forgetting to TRIM before LEN, LEFT/RIGHT, or comparisons

   • ' John ' ≠ 'John'. Use TRIM(first_name) for cleaner logic.

3. Losing NULL handling in concatenation

   • FirstName + ' ' + LastName becomes NULL if LastName is NULL.
   • Use COALESCE(LastName, '') or CONCAT(FirstName, ' ', LastName).

4. Interpreting FORMAT output as dates/numbers

   • FORMAT returns strings; don’t use formatted values for numeric sorting or date math. Use FORMAT only in SELECT for display.

5. Using the wrong date part function

   • You want integer? Use DATEPART/YEAR/MONTH/DAY.
   • You want name? Use DATENAME.
   • You want “start of period”? Use DATETRUNC. The “How to choose the right function?” slide summarises this.

6. Dividing by 0 without NULLIF

   • Always guard denominators that may be 0: value / NULLIF(den, 0).

7. Not distinguishing NULL vs empty vs spaces

   • Treat ' ', '', and NULL separately if needed; use TRIM, NULLIF, COALESCE.

8. Using CASE quick form incorrectly

   • In quick form, all WHEN clauses compare to one expression: CASE Country WHEN 'Germany' THEN ...
   • Don’t put full conditions like WHEN Country = 'Germany' in quick form (that’s for searched CASE, the full form). Slides show the difference explicitly.

7. Memory boosters (mnemonics & associations)

7.1 String functions

• “Cute Upper Lower Trimmed Robots Learn Little Right Stories”

  • CONCAT
  • UPPER
  • LOWER
  • TRIM
  • REPLACE
  • LEN
  • LEFT
  • RIGHT
  • SUBSTRING

• Visual: imagine a factory on the string-functions slide (page 7):

  • Manipulation room → CONCAT, UPPER, LOWER, TRIM, REPLACE
  • Calculation room → LEN
  • Extraction room → LEFT, RIGHT, SUBSTRING.

7.2 Numeric functions

• RA for numbers: ROUND, ABS.

  • Round = “make it neat”; Abs = “make it positive”.

7.3 Date & time

Remember the four families from the date & time function overview slide:

• C-P-V-F:

  • Calculations → DATEADD, DATEDIFF
  • Parts → DATEPART, DATENAME, DATETRUNC, YEAR, MONTH, DAY, EOMONTH
  • Validation → ISDATE
  • Formatting & Casting → FORMAT, CONVERT, CAST

7.4 NULL functions

• I Can Not Ignore NULLs

  • ISNULL
  • COALESCE
  • NULLIF
  • IS NULL / IS NOT NULL

7.5 CASE

Think of CASE as a decision flowchart (like the arrows on the CASE slides):

CASE = Check conditions → pick the first true → stop → return result; otherwise use ELSE.

8. Active-recall questions & mini-exercises

Try answering these without looking; then check with the notes/scripts.

8.1 String functions

1. Write a query that outputs FirstName and a column Initial with only the first character (no leading spaces).
2. How would you detect rows in customers where first_name has leading/trailing spaces?
3. What is the difference between SUBSTRING(first_name, 2, 3) and RIGHT(first_name, 3)?

8.2 Numeric functions

4. What does ROUND(3.516, 1) return? What about ROUND(3.516, 0)?
5. Give an example where ABS() is helpful in a report.

8.3 Date & time

6. Write a query to show, for each order, OrderDate and the year start date of that order (use DATETRUNC).
7. How do DATEPART(month, OrderDate) and DATENAME(month, OrderDate) differ?
8. How can you count how many orders were placed in each calendar year using YEAR()?
9. Format CreationTime as "Wed 20-08-2025" using FORMAT.
10. Convert CreationTime to DATE using CONVERT and also using CAST.
11. Write a query to show OrderDate, ShipDate, and the number of days between them.
12. How would you find only the rows where a text column might be an invalid date string, using ISDATE?

8.4 NULL handling

13. Using Sales.Customers, show FullName composed of FirstName + space + LastName, even if LastName is NULL.
14. What’s the difference between COALESCE(Score, 0) and ISNULL(Score, 0) conceptually?
15. How can you safely compute Sales / Quantity when Quantity might be 0?
16. Write an ORDER BY that sorts by Score ascending, but puts NULL scores last.
17. In the NULL vs empty vs blank example, what does NULLIF(TRIM(Category), '') do?

8.5 CASE

18. Write a CASE expression that labels each order as 'Big' when Sales > 100, 'Medium' when Sales between 50 and 100, else 'Small'.
19. Convert country names 'Germany' and 'USA' to codes 'DE' and 'US' using quick form CASE.
20. How would you use CASE inside SUM() to count how many orders have Sales > 30 per customer?

9. Ultra-short recap (exam-style checklist)

• Single-row functions = one output per row; can be nested.
• String functions: CONCAT, UPPER, LOWER, TRIM, REPLACE, LEN, LEFT, RIGHT, SUBSTRING – think manipulate, measure, extract.
• Numeric functions: ROUND for precision, ABS for magnitude.
• Date & time grouped into: parts (DATEPART/DATENAME/DATETRUNC/YEAR/MONTH/DAY/EOMONTH), calculations (DATEADD/DATEDIFF), validation (ISDATE), formatting & casting (FORMAT/CONVERT/CAST).
• Use FORMAT for display only; underlying types remain date/number.
• NULLs: use COALESCE/ISNULL/NULLIF and IS NULL / IS NOT NULL; know difference between NULL, empty string, space.
• CASE adds conditional logic for categorizing, mapping, cleaning NULLs, and conditional aggregation.
• Always guard against division by zero and mis-handling NULLs.